<?php
//Rquire MySqlAdapter.
require_once(dirname(__FILE__).'/../db/MySqlAdapter.php');

/**
 * @access public
 */

$mysql_link=NULL;
	
if (is_null($mysql_link))
	{
	 
		$mysql_link =  MySqlAdapter::connect1();		
		//Select Database
     	if ($mysql_link):
			$result = MySqlAdapter::connect2();
		else:
			die ("Could not connect to db " . mysql_error());
		endif;
		if(!$result){
			/************************************* CREATING DATABASES ****************************************************/
		$result = mysql_query("CREATE DATABASE ohel_shelomo_2012")or die ("Failed to create database: " . mysql_error());
		//Seting a link to our main database  after creating it
		$result = mysql_select_db('ohel_shelomo_2012') or die ("Could not select the schema db_exm: " . mysql_error());
		
		// CREATING TABLE PERSON		
		$result = mysql_query("create table person(id int(9) primary key not null, first_name varchar(100) not null,
		 last_name varchar(100) not null, adress varchar(100), phone varchar(20), email varchar(300), type varchar(20))") or die ("Invalid: " . mysql_error());

		 
		 // CREATING TABLE STUDENTS DETAILS AND CLASSES DETAILS.

		 $result = mysql_query("create table security (id int(10) primary key not null, username varchar(20),password varchar(10))") or die ("Invalid: " . mysql_error());
		 


		// CREATING TABLE room		
		$result = mysql_query("CREATE TABLE IF NOT EXISTS `room` (  `number` int(5) NOT NULL,  `comment` varchar(45) NOT NULL,  KEY `number` (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;") or die ("Invalid: " . mysql_error());
	 		 

		 // CREATING TABLE contactus
		 $result = mysql_query("create table contactus (  `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `name` varchar(30) NOT NULL DEFAULT 'name',  `email` varchar(30) NOT NULL,  `contact` varchar(10000) NOT NULL DEFAULT 'contact',  PRIMARY KEY (`dateadded`,`name`)
)") or die ("Invalid: " . mysql_error());

		 // CREATING TABLE STUDENTS
		 $result = mysql_query("CREATE TABLE IF NOT EXISTS `students` (  `id` int(9) NOT NULL,  `first_class` varchar(100) DEFAULT NULL,
  `second_class` varchar(100) DEFAULT NULL,  `third_class` varchar(100) DEFAULT NULL,  `room` int(5) NOT NULL,  PRIMARY KEY (`id`),
  KEY `room` (`room`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;") or die ("Invalid: " . mysql_error());
		 
		 // CREATING TABLE CLASSES 
		 $result = mysql_query("CREATE TABLE IF NOT EXISTS `classes` (  `name` varchar(30) NOT NULL,  `day` varchar(10) DEFAULT NULL,
  `start_time` time DEFAULT NULL,  `end_time` time DEFAULT NULL,  `teacher` varchar(20) DEFAULT NULL,  PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;") or die ("Invalid: " . mysql_error());
		 
		 // CREATING TABLE reportus
		 $result = mysql_query("CREATE TABLE IF NOT EXISTS `report` ( `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `url` varchar(200) NOT NULL COMMENT 'the url that the error report is about',  `fullName` varchar(25) NOT NULL,
  `email` varchar(45) NOT NULL COMMENT 'the reporter email',  `phone` int(45) NOT NULL COMMENT 'the reporter phone number',  `issue` varchar(25) NOT NULL COMMENT 'the issue of the reprot',
  `subject` varchar(45) NOT NULL, `description` varchar(10000) NOT NULL,  PRIMARY KEY (`dateadded`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;") or die ("Invalid: " . mysql_error());		 	 

		 // CREATING TABLE equipment
		 $result = mysql_query("CREATE TABLE IF NOT EXISTS `equipment` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(150) NOT NULL,  `description` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;") or die ("Invalid: " . mysql_error());		 	 
		 		 
		 // CREATING TABLE reservation
		 $result = mysql_query("CREATE TABLE IF NOT EXISTS `reservation` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `equipmentID` int(11) NOT NULL,  `userID` int(11) NOT NULL COMMENT 'user id who order the equipment',
  `lentDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'start date of lent',  `returnDate` timestamp NULL DEFAULT NULL COMMENT 'end date of lent',  `wastaken` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'was the item taken by the user ? true/false',
  `wasreturned` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'was the item was the item returned by the user ? true/falseby the user ? true/false',
  PRIMARY KEY (`id`),  KEY `equipmentID` (`equipmentID`,`userID`),  KEY `reservation_ibfk_2` (`userID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='reservation of equipments' AUTO_INCREMENT=1 ;") or die ("Invalid: " . mysql_error());
  
			/************************************* CONSTRAINTS FOR DUMPED TABLES *********************************************/			
		 // Constraints for table reservation
		 $result = mysql_query("ALTER TABLE `reservation`  	ADD CONSTRAINT `reservation_ibfk_2` FOREIGN KEY (`userID`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,	
ADD CONSTRAINT `reservation_ibfk_1` FOREIGN KEY (`equipmentID`) REFERENCES `equipment` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;") or die ("Invalid: " . mysql_error());		 	   

		 // Constraints for table `students`
		 $result = mysql_query("ALTER TABLE `students`
  ADD CONSTRAINT `students_ibfk_2` FOREIGN KEY (`room`) REFERENCES `room` (`number`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `students_ibfk_1` FOREIGN KEY (`id`) REFERENCES `person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;") or die ("Invalid: " . mysql_error());
		
			/***************************************** UPDATE DATA ***********************************************************/			

/* script for getting the path to the www root folder on the server			
$path =  $_SERVER['DOCUMENT_ROOT'];
echo "WWW path is:".$path."<br/>";
*/
		 // UPDATE DATA TABLE room
		$query = "LOAD DATA INFILE \"..\\\\..\\\\..\\\\..\\\\www\\\\WebCourse1\\\\db\\\\import_data\\\\text\\\\room.txt\" INTO TABLE room FIELDS TERMINATED BY \"\\t\""; 
mysql_query($query) 
	or die ("Invalid insert " . mysql_error());
			
		 // UPDATE DATA TABLE CLASSES
		$query = "LOAD DATA INFILE \"..\\\\..\\\\..\\\\..\\\\www\\\\WebCourse1\\\\db\\\\import_data\\\\text\\\\classes.txt\" INTO TABLE classes FIELDS TERMINATED BY \"\\t\""; 
mysql_query($query) 
	or die ("Invalid insert " . mysql_error());

		 // UPDATE DATA TABLE CONTACTUS
		$query = "LOAD DATA INFILE \"..\\\\..\\\\..\\\\..\\\\www\\\\WebCourse1\\\\db\\\\import_data\\\\text\\\\contact-us.txt\" INTO TABLE contactus FIELDS TERMINATED BY \"\\t\""; 
mysql_query($query) 
	or die ("Invalid insert " . mysql_error());

		 // UPDATE DATA TABLE PERSON
		$query = "LOAD DATA INFILE \"..\\\\..\\\\..\\\\..\\\\www\\\\WebCourse1\\\\db\\\\import_data\\\\text\\\\person.txt\" INTO TABLE person FIELDS TERMINATED BY \"\\t\""; 
mysql_query($query) 
	or die ("Invalid insert " . mysql_error());
	//UPDATING TABLE SECURITY
$query = "LOAD DATA INFILE \"..\\\\..\\\\..\\\\..\\\\www\\\\WebCourse1\\\\db\\\\import_data\\\\text\\\\security.txt\" INTO TABLE security FIELDS TERMINATED BY \"\\t\""; 
mysql_query($query) 
	or die ("Invalid insert " . mysql_error());

		 // UPDATE DATA TABLE equipment
		$query = "LOAD DATA INFILE \"..\\\\..\\\\..\\\\..\\\\www\\\\WebCourse1\\\\db\\\\import_data\\\\text\\\\equipment.txt\" INTO TABLE equipment FIELDS TERMINATED BY \"\\t\""; 
mysql_query($query) 
	or die ("Invalid insert " . mysql_error());
	
		 // UPDATE DATA TABLE STUDENTS
		$query = "LOAD DATA INFILE \"..\\\\..\\\\..\\\\..\\\\www\\\\WebCourse1\\\\db\\\\import_data\\\\text\\\\students.txt\" INTO TABLE students FIELDS TERMINATED BY \"\\t\""; 
mysql_query($query) 
	or die ("Invalid insert " . mysql_error());
		}		
	}
		 
	
	 

if (!is_null($mysql_link))
	{
	mysql_close($mysql_link);
	$mysql_link = NULL;			
	}

	
	
	
	
	
	 

?>